<?php

namespace App\Repository;

use App\Entity\Page;
use App\Entity\PageComment;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Security\Core\Security;

/**
 * @method PageComment|null find($id, $lockMode = null, $lockVersion = null)
 * @method PageComment|null findOneBy(array $criteria, array $orderBy = null)
 * @method PageComment[]    findAll()
 * @method PageComment[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class PageCommentRepository extends ServiceEntityRepository {
    private $security;

    public function __construct (ManagerRegistry $registry, Security $security) {
        parent::__construct($registry, PageComment::class);
        $this->security = $security;
    }

    /**
     * 根据文章ID获取评论数
     * @param $page_id
     * @return mixed
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getCountByPageId ($page_id) {
        return $this->createQueryBuilder('p')
            ->select('count(p.id)')
            ->andWhere('p.page_id = :page_id')
            ->andWhere('p.status = :status')
            ->setParameter('page_id', $page_id)
            ->setParameter('status', 1)
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * 获取评论数
     * @param bool $all
     * @return mixed
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getCommentTotal ($all = false) {
        $query = $this->createQueryBuilder('p')
            ->select('count(p.id)');

        if (!$all && !$this->security->getUser()->isSuperAdmin()) {
            $page_ids = $this->getEntityManager()->getRepository(Page::class)->getPageIds();
            if ($page_ids) {
                $ids = [];
                foreach ($page_ids as $item) {
                    array_push($ids, $item['id']);
                }
                $query->andWhere('p.page_id IN ('.implode(",", $ids).')');
            } else {
                $query->andWhere('p.page_id = 0');
            }
        }

        return $query->getQuery()->getSingleScalarResult();
    }

    /**
     * 获取最新评论
     * @param int $limit
     * @param bool $is_auth
     * @return mixed
     */
    public function getNewComment ($limit = 5, $is_auth = false) {
        $query = $this->createQueryBuilder('p')
            ->orderBy('p.id', 'DESC')
            ->setMaxResults($limit);

        if ($is_auth) {
            if (!$this->security->getUser()->isSuperAdmin()) {
                $page_ids = $this->getEntityManager()->getRepository(Page::class)->getPageIds();
                if ($page_ids) {
                    $ids = [];
                    foreach ($page_ids as $item) {
                        array_push($ids, $item['id']);
                    }
                    $query->andWhere('p.page_id IN ('.implode(",", $ids).')');
                } else {
                    $query->andWhere('p.page_id = 0');
                }
            }
        }

        return $query->getQuery()->getResult();
    }

    /**
     * 评论-分页
     * @param $id
     * @param $limit
     * @param $offset
     * @return mixed[]
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getCommentByPaginator ($id, $limit, $offset) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.parent_id,p.nickname,p.comment,p.created_at,c.nickname as parent_nickname,c.comment as parent_comment
                from page_comment as p 
                left join page_comment as c on p.parent_id = c.id 
                where p.status = 1 and p.page_id = '.$id.' order by id desc limit '.($offset * $limit).','.$limit;
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 根据ID获取评论内容
     * @param $id
     * @return mixed
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function getCommentByById ($id) {
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT p.id,p.parent_id,p.nickname,p.comment,p.created_at,c.nickname as parent_nickname,c.comment as parent_comment
                from page_comment as p 
                left join page_comment as c on p.parent_id = c.id 
                where p.status = 1 and p.id = '.$id;
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * 获取所有未审核的数量
     * @return mixed
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getPendingTotal () {
        return $this->createQueryBuilder('p')
            ->select('count(p.id)')
            ->where('p.status = 0')
            ->getQuery()
            ->getSingleScalarResult();
    }
}
